//引入express第三方模块
const express = require("express");
//创建路由器对象
const r = express.Router();
//引入连接数据库,不是中间件不能写在app.js中
const pool = require("../mysql.js");
const fs = require("fs");
const path = require("path");

//试卷，出卷
r.post("/sj", (req, res, next) => {
  // console.log(req.body.sj);
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("没有教师信息");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        pool.query(
          "insert into exam (e_sj,eid,school,faculty) values (?,?,?,?)",
          [req.body.sj, req.body.eid, result[0].school, result[0].faculty],
          (err, result) => {
            if (err) {
              next(err); //抛给下一个中间件来处理
              //阻止往后执行
              return;
            }
            console.log("插入1");
            res.send({
              status: 200,
              msg: "插入成功",
            });
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//试卷查询
r.get("/examsearch", (req, res, next) => {
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("没有教师信息");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        pool.query(
          "select * from exam where school=? and faculty=?",
          [result[0].school, result[0].faculty],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("查询失败");
              res.send({ status: 201, msg: "查询失败" });
            } else {
              res.send({ status: 200, msg: "查询成功", data: result });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//查询题目
r.get("/searchTm/:eid", (req, res, next) => {
  // console.log(req.params.eid)
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("没有教师信息");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        pool.query(
          "select e_sj from exam where eid=? and school=? and faculty=?",
          [req.params.eid, result[0].school, result[0].faculty],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("查询失败");
              res.send({ status: 201, msg: "查询失败" });
            } else {
              res.send({ status: 200, msg: "查询成功", data: result });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//获取专业
r.get("/getmajor", (req, res, next) => {
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("没有教师信息");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        pool.query(
          "select distinct major from schoolinfo where school=? and faculty=?",
          [result[0].school, result[0].faculty],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }

            if (result.length == 0) {
              console.log("没有班级信息");
              res.send({
                status: 201,
                msg: "查询失败",
              });
            } else {
              res.send({
                status: 200,
                msg: "查询成功",
                data: result,
              });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//根据专业查询班级
r.get("/selectclassroom/:major", (req, res, next) => {
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("没有教师信息");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        pool.query(
          "select * from classroom where school=? and faculty=? and major=?",
          [result[0].school, result[0].faculty, req.params.major],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }

            if (result.length == 0) {
              console.log("没有班级信息");
              res.send({
                status: 201,
                msg: "查询失败",
              });
            } else {
              res.send({
                status: 200,
                msg: "查询成功",
                data: result,
              });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//查询班级
r.get("/selectclassroom", (req, res, next) => {
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("没有教师信息");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        pool.query(
          "select * from classroom where school=? and faculty=? ",
          [result[0].school, result[0].faculty],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }

            if (result.length == 0) {
              console.log("没有班级信息");
              res.send({
                status: 201,
                msg: "查询失败",
              });
            } else {
              res.send({
                status: 200,
                msg: "查询成功",
                data: result,
              });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//添加班级
r.post("/addclassroom", (req, res, next) => {
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("没有教师信息");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        pool.query(
          "insert into classroom (school,faculty,major,classroom) values(?,?,?,?)",
          [
            result[0].school,
            result[0].faculty,
            req.body.major,
            req.body.classroom,
          ],
          (err, result) => {
            if (err) {
              next(err); //抛给下一个中间件来处理
              //阻止往后执行
              return;
            }
            console.log("添加班级成功");
            res.send({
              status: 200,
              msg: "插入成功",
            });
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//修改班级
r.put("/updateclassroom", (req, res, next) => {
  pool.query(
    "update classroom set classroom=?,major=? where c_id=?",
    [req.body.classroom, req.body.major, req.body.c_id],
    (err, result) => {
      if (err) {
        next(err); //抛给下一个中间件来处理
        //阻止往后执行
        return;
      }
      console.log("修改班级成功");
      res.send({
        status: 200,
        msg: "修改成功",
      });
    }
  );
  pool.releaseConnection(pool);
});

//删除班级
r.delete("/deleteclassroom/:classroom", (req, res, next) => {
  pool.query(
    "delete from classroom where classroom=?",
    [req.params.classroom],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      if (result.affectedRows == 0) {
        res.send({
          status: 201,
          msg: "删除失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "删除成功",
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//发布考试信息
r.post("/examinfo", (req, res, next) => {
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("查询失败");
        res.send({ status: 201, msg: "查询失败" });
      } else {
        pool.query(
          "insert into examinfo (e_name,school,faculty,major,classroom,eid,start_time,end_time) values (?,?,?,?,?,?,?,?)",
          [
            req.body.testname,
            result[0].school,
            result[0].faculty,
            req.body.major,
            req.body.classroom,
            req.body.selecteid,
            req.body.starttime,
            req.body.endtime,
          ],
          (err, result) => {
            if (err) {
              next(err); //抛给下一个中间件来处理
              //阻止往后执行
              return;
            }
            console.log("发布考试信息成功");
            res.send({
              status: 200,
              msg: "插入成功",
            });
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//查询已发布的考试信息
r.get("/examinfo", (req, res, next) => {
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("查询失败");
        res.send({ status: 201, msg: "查询失败" });
      } else {
        pool.query(
          "select * from examinfo where school=? and faculty=?",
          [result[0].school, result[0].faculty],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("查询失败");
              res.send({ status: 201, msg: "查询失败" });
            } else {
              res.send({ status: 200, msg: "查询成功", data: result });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//删除已发布的考试信息
r.delete("/deletetest/:testname", (req, res, next) => {
  pool.query(
    "delete from examinfo where e_name=?",
    [req.params.testname],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      if (result.affectedRows == 0) {
        res.send({
          status: 201,
          msg: "删除考试信息失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "删除成功",
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//修改考试时间
r.put("/examtime", (req, res, next) => {
  pool.query(
    "update examinfo set start_time=?,end_time=? where e_name=?",
    [req.body.starttime, req.body.endtime, req.body.ename],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      if (result.affectedRows == 0) {
        res.send({
          status: 201,
          msg: "修改失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "修改成功",
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//查询所有学生信息
r.get("/searchstudentinfo", (req, res, next) => {
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("查询失败");
        res.send({ status: 201, msg: "查询失败" });
      } else {
        pool.query(
          "select * from studentinfomation where school=? and faculty=?",
          [result[0].school, result[0].faculty],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("查询失败");
              res.send({ status: 201, msg: "查询学生信息失败" });
            } else {
              res.send({ status: 200, msg: "查询成功", data: result });
            }
          }
        );
      }
    }
  );
  pool.releaseConnection(pool);
});

//按姓名查找学生信息
//select u_name,phone,sex,birthday,email,classroom,name,studentid from studentinfomation where name=?
r.get("/bynamesearchstudentinfo/:name", (req, res, next) => {
  //模糊查询 select * from studentinfomation where name like '%肖%'
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("查询失败");
        res.send({ status: 201, msg: "查询失败" });
      } else {
        pool.query(
          "select * from studentinfomation where name like ? and school=? and faculty=?",
          [`%${req.params.name}%`, result[0].school, result[0].faculty],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("通过姓名查询学生信息失败");
              res.send({ status: 201, msg: "通过姓名查询学生信息失败" });
            } else {
              res.send({ status: 200, msg: "查询成功", data: result });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//查找学生考试信息
r.get("/searchstudentexaminfo", (req, res, next) => {
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("查询失败");
        res.send({ status: 201, msg: "查询失败" });
      } else {
        pool.query(
          "select * from studentexaminfo where school=? and faculty=?",
          [result[0].school, result[0].faculty],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("查询学生考试信息失败");
              res.send({ status: 201, msg: "查询学生考试信息失败" });
            } else {
              res.send({ status: 200, msg: "查询成功", data: result });
            }
          }
        );
      }
    }
  );
  pool.releaseConnection(pool);
});

//删除学生考试信息
r.delete("/deletestudentexaminfo/:name&:e_name", (req, res, next) => {
  pool.query(
    "delete from studentexaminfo where name=? and e_name=?",
    [req.params.name, req.params.e_name],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      if (result.affectedRows == 0) {
        res.send({
          status: 201,
          msg: "删除学生考试信息失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "删除成功",
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//通过姓名查找学生考试信息
//select * from studentexaminfo where name=?
r.get("/bynamesearchstudentexaminfo/:name", (req, res, next) => {
  //模糊查询 select * from studentinfomation where name like '%肖%'
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("查询失败");
        res.send({ status: 201, msg: "查询失败" });
      } else {
        pool.query(
          "select * from studentexaminfo where name like ? and school=? and faculty=?",
          [`%${req.params.name}%`, result[0].school, result[0].faculty],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("通过姓名查询学生考试信息失败");
              res.send({ status: 201, msg: "通过姓名查询学生信息失败" });
            } else {
              res.send({ status: 200, msg: "查询成功", data: result });
            }
          }
        );
      }
    }
  );
  pool.releaseConnection(pool);
});

//查询学校
//查询学校
r.get("/schoolinfo", (req, res, next) => {
  pool.query("select distinct school from schoolinfo", (err, result) => {
    if (err) {
      next(err);
      return;
    }
    // console.log(result);
    if (result.length == 0) {
      console.log("没有该学校");
      res.send({
        status: 201,
        msg: "查询失败",
      });
    } else {
      res.send({
        status: 200,
        msg: "查询成功",
        data: result,
      });
    }
  });
  pool.releaseConnection(pool);
});

//查询学校对应院系
r.get("/schoolinfo/:school", (req, res, next) => {
  pool.query(
    "select distinct faculty from schoolinfo where school=?",
    [req.params.school],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("没有该院系");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "查询成功",
          data: result,
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//提交教师信息，先把user表的信息拿出来,把user表的信息插入
r.post("/teacherinfomation", (req, res, next) => {
  pool.query(
    "select * from user where username=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("该用户信息为空");
      } else {
        pool.query(
          "insert into teacherinfomation (u_name,phone,sex,birthday,email,name,teacherid,school,faculty) values(?,?,?,?,?,?,?,?,?)",
          [
            result[0].username,
            result[0].phone,
            result[0].sex,
            result[0].birthday,
            result[0].email,
            req.body.name,
            req.body.teacherid,
            req.body.school,
            req.body.faculty,
          ],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }

            res.send({
              status: 200,
              msg: "插入成功",
            });
          }
        );
      }
    }
  );
  pool.releaseConnection(pool);
});

//查询教师信息
r.get("/teacherinfomation", (req, res, next) => {
  pool.query(
    "select * from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      if (result.length == 0) {
        console.log("没有该教师数据");
        res.send({
          status: 201,
          msg: "查询失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "查询成功",
          data: result,
        });
      }
    }
  );
  pool.releaseConnection(pool);
});


//修改教师信息
r.put("/teacherinfomation", (req, res, next) => {
  // console.log(req.body)
  //修改学信息标同时也应该修改user表对应的数据(为避免信息表信息已修改而user表信息未修改的情况，要先修改user表)
  pool.query(
    "update user set phone=?,sex=?,birthday=?,email=? where username=?",
    [
      req.body.phone,
      req.body.sex,
      req.body.birthday,
      req.body.email,
      req.cookies.userinfo.username,
    ],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }

      //update 更新多个字段时不能用and 只能用,号隔开
      pool.query(
        "update teacherinfomation set phone=?,sex=?,birthday=?,email=?,name=?,teacherid=?,school=?,faculty=? where u_name=?",
        [
          req.body.phone,
          req.body.sex,
          req.body.birthday,
          req.body.email,
          req.body.name,
          req.body.teacherid,
          req.body.school,
          req.body.faculty,
          req.cookies.userinfo.username,
        ],
        (err, result) => {
          if (err) {
            next(err);
            return;
          }

          if (result.affectedRows == 0) {
            res.send({
              status: 201,
              msg: "修改失败",
            });
          } else {
            res.send({
              status: 200,
              msg: "修改成功",
            });
          }
        }
      );
    }
  );

  pool.releaseConnection(pool);
});

//删除教师信息
r.delete("/teacherinfomation", (req, res, next) => {
  pool.query(
    "delete from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      if (result.affectedRows == 0) {
        res.send({
          status: 201,
          msg: "删除学生信息失败",
        });
      } else {
        res.send({
          status: 200,
          msg: "删除成功",
        });
      }
    }
  );
  pool.releaseConnection(pool);
});

//查询对应班级已发布的考试
r.get("/classeid/:classroom", (req, res, next) => {
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("查询失败");
        res.send({ status: 201, msg: "查询失败" });
      } else {
        pool.query(
          "select eid from examinfo where school=? and faculty=? and classroom=?",
          [result[0].school, result[0].faculty, req.params.classroom],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("查询失败");
              res.send({ status: 201, msg: "查询失败" });
            } else {
              res.send({ status: 200, msg: "查询成功", data: result });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//通过eid和专业查询学生考试试卷
r.get("/studentexaminfo/:eid&:major&:classroom", (req, res, next) => {
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("查询失败");
        res.send({ status: 201, msg: "查询失败" });
      } else {
        pool.query(
          "select answer from studentexaminfo where eid=? and school=? and faculty=? and major=? and classroom",
          [
            req.params.eid,
            result[0].school,
            result[0].faculty,
            req.params.major,
            req.params.classroom,
          ],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("查询失败");
              res.send({ status: 201, msg: "查询失败" });
            } else {
              res.send({ status: 200, msg: "查询成功", data: result });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//批改试卷
r.put("/updatestudentexaminfo", (req, res, next) => {
  pool.query(
    "select school,faculty from teacherinfomation where u_name=?",
    [req.cookies.userinfo.username],
    (err, result) => {
      if (err) {
        next(err);
        return;
      }
      // console.log(result);
      if (result.length == 0) {
        console.log("查询失败");
        res.send({ status: 201, msg: "查询失败" });
      } else {
        pool.query(
          "update studentexaminfo set score=?,answer=? where school=? and faculty=? and major=? and classroom=? and eid=?",
          [
            req.body.score,
            req.body.arr,
            result[0].school,
            result[0].faculty,
            req.body.major,
            req.body.classroom,
            req.body.eid,
          ],
          (err, result) => {
            if (err) {
              next(err);
              return;
            }
            // console.log(result);
            if (result.length == 0) {
              console.log("查询失败");
              res.send({ status: 201, msg: "查询失败" });
            } else {
              res.send({ status: 200, msg: "查询成功", data: result });
            }
          }
        );
      }
    }
  );

  pool.releaseConnection(pool);
});

//导出路由器对象
module.exports = r;
